# /app/__init__.py
from flask import Flask
from app import config
from app.config import create_db_connection

def create_app():

    app = Flask(__name__)
    app.secret_key = b'_5#y2L"F4Q8z\n\xec]/'

    create_db_table()

    from .main.views import main_bp
    app.register_blueprint(main_bp)

    from app.system_settings.system_settings import system_settings_bp
    app.register_blueprint(system_settings_bp)


    # 添加客户与客户列表蓝图
    from .customers.add_customers import add_customers_bp
    app.register_blueprint(add_customers_bp)
    from .customers.list_customers import list_customers_bp
    app.register_blueprint(list_customers_bp)

    # 注册列表蓝图
    from .class_list.class_list import class_list_bp
    app.register_blueprint(class_list_bp)

    # 添加产品与产品列表蓝图
    from .product.add_product import add_product_bp
    app.register_blueprint(add_product_bp)
    from .product.list_product import list_product_bp
    app.register_blueprint(list_product_bp)

    from .warehousing_entry.warehousing_entry_add import warehousing_entry_add_bp
    app.register_blueprint(warehousing_entry_add_bp)
    from .warehousing_entry.warehousing_entry_list import warehousing_entry_list_bp
    app.register_blueprint(warehousing_entry_list_bp)

    from .inventory.inventory import inventory_bp
    app.register_blueprint(inventory_bp)

    from .outbound_order.outbound_order_add import outbound_order_add_bp
    app.register_blueprint(outbound_order_add_bp)
    from .outbound_order.outbound_order_list import outbound_order_list_bp
    app.register_blueprint(outbound_order_list_bp)

    from app.delivery_note.delivery_note import delivery_note_bp
    app.register_blueprint(delivery_note_bp)

    return app


def create_db_table():
    conn, cursor = create_db_connection()

    # 创建用户表
    cursor.execute('''
          CREATE TABLE IF NOT EXISTS users (
          id INT AUTO_INCREMENT PRIMARY KEY,
          username VARCHAR(50) UNIQUE,
          password VARCHAR(50)
      )''')

    # 添加 admin 用户
    add_admin_query = "INSERT INTO users (username, password) VALUES (%s, %s)"
    admin_username = 'admin'
    admin_password = 'admin'

    # 查询 admin 用户是否已存在
    query_admin_query = "SELECT * FROM users WHERE username = %s"
    cursor.execute(query_admin_query, (admin_username,))
    result = cursor.fetchone()
    if not result:
        cursor.execute(add_admin_query, (admin_username, admin_password))

    # 创建客户表
    cursor.execute('''
    CREATE TABLE IF NOT EXISTS customer_list (
        id INT AUTO_INCREMENT PRIMARY KEY,
        customer_name VARCHAR(255) NOT NULL,
        creditCode VARCHAR(255) NOT NULL,
        address VARCHAR(255) NOT NULL,
        contactPerson VARCHAR(255) NOT NULL,
        contactNumber VARCHAR(255) NOT NULL
    )
    ''')
    # 创建类别表
    cursor.execute("""
    CREATE TABLE IF NOT EXISTS class_list(
        id INT AUTO_INCREMENT PRIMARY KEY,
        NAME VARCHAR(255) NOT NULL,
        CODE VARCHAR(255) NOT NULL,
        parent_directory INT NULL,
        FOREIGN KEY(parent_directory) REFERENCES class_list(id)
    )
    """)

    # 创建产品表
    cursor.execute("""
                CREATE TABLE IF NOT EXISTS product_list (
                  id INT AUTO_INCREMENT PRIMARY KEY,
                  customer_id INT,
                  class_id INT,
                  product_name VARCHAR(255),
                  product_model VARCHAR(255),
                  product_color VARCHAR(255),
                  product_unit VARCHAR(255),
                  purchase_price DECIMAL(10, 2),
                  sales_price DECIMAL(10, 2),
                  image_path VARCHAR(255),  -- 添加了image_path字段
                  INDEX customer_id_idx (customer_id),
                  CONSTRAINT fk_customer_id
                    FOREIGN KEY (customer_id)
                    REFERENCES customer_list (id)
                    ON UPDATE CASCADE
                )
            """)

    # 创建入库单列表
    cursor.execute('''CREATE TABLE IF NOT EXISTS warehousing_entry_list (
      id INT AUTO_INCREMENT PRIMARY KEY,
      customer_id INT,
      enter_time DATE,
      document_number VARCHAR(255) UNIQUE,
      FOREIGN KEY (customer_id) REFERENCES customer_list(id)
    )''')

    # 创建入库单详情表
    cursor.execute('''CREATE TABLE IF NOT EXISTS warehousing_entry_info (
      id INT AUTO_INCREMENT PRIMARY KEY,
      document_number VARCHAR(255),
      product_id INT,
      quantity INT,
      purchase_price  DOUBLE,
      FOREIGN KEY (product_id) REFERENCES product_list(id),
      FOREIGN KEY (document_number) REFERENCES warehousing_entry_list(document_number)
    )''')

    # 创建入库单附件表
    cursor.execute('''CREATE TABLE IF NOT EXISTS warehousing_entry_attachment (
      id INT AUTO_INCREMENT PRIMARY KEY,
      document_number VARCHAR(255),
      filename VARCHAR(255),
      FOREIGN KEY (document_number) REFERENCES warehousing_entry_list(document_number)
    )''')

    # 创建出库单列表
    cursor.execute('''CREATE TABLE IF NOT EXISTS outbound_order_list (
      id INT AUTO_INCREMENT PRIMARY KEY,
      customer_id INT,
      enter_time DATE,
      document_number VARCHAR(255) UNIQUE,
      FOREIGN KEY (customer_id) REFERENCES customer_list(id)
    )''')

    # 创建出库单详情表
    cursor.execute('''CREATE TABLE IF NOT EXISTS outbound_order_info (
      id INT AUTO_INCREMENT PRIMARY KEY,
      document_number VARCHAR(255),
      product_id INT,
      quantity INT,
      sales_price  DOUBLE,
      FOREIGN KEY (product_id) REFERENCES product_list(id),
      FOREIGN KEY (document_number) REFERENCES outbound_order_list(document_number)
    )''')

    # 创建出库单附件表
    cursor.execute('''CREATE TABLE IF NOT EXISTS outbound_order_attachment (
      id INT AUTO_INCREMENT PRIMARY KEY,
      document_number VARCHAR(255),
      filename VARCHAR(255),
      FOREIGN KEY (document_number) REFERENCES outbound_order_list(document_number)
    )''')

    # 创建库存表
    cursor.execute('''CREATE TABLE IF NOT EXISTS inventory(
      id INT AUTO_INCREMENT PRIMARY KEY,
      product_id INT,
      quantity INT,
      FOREIGN KEY (product_id ) REFERENCES product_list(id)
    )''')

    conn.commit()
    cursor.close()
    conn.close()